﻿------------------------- STEP 1: KÉO DỮ LIỆU TỪ PHANH--------------------------
--DROP TABLE LOAN_EOP_LOAI_QUICKWIN_20161031
DECLARE @MONTH1 DATE=DATEADD(S,-1,DATEADD(MM, DATEDIFF(M,0,(SELECT MAX_DATE_ENDMONTH FROM DATE_EM_REPORT))-10,0)) --'20151130'
--PRINT (@MONTH1)
DECLARE @MONTH2 NVARCHAR(6)
DECLARE @COUNT INT =2
DECLARE @FULLDATE NVARCHAR(8)
declare @datecompare date = convert(date, '20170331')

--SET @MONTH1 = DATEADD(M,1,@MONTH1)
WHILE @MONTH1 <=(SELECT MAX_DATE_ENDMONTH FROM DATE_EM_REPORT)
BEGIN
print (@MONTH1)
SET @MONTH2=CONVERT(NVARCHAR(6), @MONTH1, 112)
	SET @FULLDATE= CONVERT (NVARCHAR(8), @MONTH1 ,112)
	IF(@MONTH1 = '20160131') SET @FULLDATE = '20160130'
	IF(@MONTH1 = '20160430') SET @FULLDATE = '20160429'
	IF(@MONTH1 = '20160731') SET @FULLDATE = '20160730'
	declare @datediff int = (DATEDIFF(day,@dateCompare,@month1))
--PRINT @MONTH1
IF NOT EXISTS
      (SELECT *
         FROM DBO.SYSOBJECTS O
        WHERE O.XTYPE IN ('U') AND O.NAME = 'LOAN_EOP_LOAI_QUICKWIN_'+@FULLDATE+'')
        BEGIN
		
		IF (@datediff<0)
EXEC('
SELECT * INTO LOAN_EOP_LOAI_QUICKWIN_'+@FULLDATE+'
FROM ANHCP.CEO_PERFORMANCE.DBO.LOAN_EOP_LOAI_QUICKWIN_'+@FULLDATE+'')

		IF @datediff>=0
		EXEC('
SELECT * INTO LOAN_EOP_LOAI_QUICKWIN_'+@FULLDATE+'
FROM ANHCP.CEO_PERFORMANCE.[dbo].LOAN_EOP_'+@FULLDATE+' where QW=''N''')

EXEC('
ALTER TABLE LOAN_EOP_LOAI_QUICKWIN_'+@FULLDATE+'
ADD CT_UUDAI_VAY NVARCHAR(100),SANPHAM_GROUP NVARCHAR(100)')

EXEC('
UPDATE A
SET A.CT_UUDAI_VAY=B.CT#UUDAI#VAY,A.SANPHAM_GROUP=B.SANPHAM_GROUP
FROM LOAN_EOP_LOAI_QUICKWIN_'+@FULLDATE+' A, (SELECT * FROM GIANGLH3.RISK.DBO.SAOKE_LUYKE WHERE MONTH='''+@MONTH2+''') B
 WHERE A.ACCTNO=B.CONTRACT_NO')
END
	--LAST DAY OF ANY MONTH AND YEAR
	SET @MONTH1 = DATEADD(S,-1,DATEADD(MM, DATEDIFF(M,0,@MONTH1)+@COUNT,0))
 END
 ---------------------------------------------------------------
 ----------STEP 2: CHƯƠNG TRÌNH SẢN PHẨM && STEP 3: CHƯƠNG TRÌNH UU DAI VAY && STEP 4: NHOM SAN PHAM
 --drop table #dao
 SET @COUNT = 1
 SELECT DAO 
INTO #DAO
FROM TBL_HR
WHERE GROUP_ID=4
AND DAO <> N'KHONG CO DAO'

DELETE FROM  #DAO WHERE DAO IS NULL
--SELECT * FROM DATE_EM_REPORT
SET @MONTH1 =DATEADD(S,-1,DATEADD(MM, DATEDIFF(M,0,(SELECT MAX_DATE_ENDMONTH FROM DATE_EM_REPORT))-10,0))
WHILE @COUNT <13
BEGIN
print (@COUNT)
SET @FULLDATE= CONVERT (NVARCHAR(8), @MONTH1 ,112)
IF(@MONTH1 = '20160131') SET @FULLDATE = '20160130'
IF(@MONTH1 = '20160430') SET @FULLDATE = '20160429'
IF(@MONTH1 = '20160731') SET @FULLDATE = '20160730'
DECLARE @STRCOUNT NVARCHAR(2) = CONVERT(NVARCHAR(2),@COUNT)

EXEC(
'IF EXISTS
      (SELECT *
         FROM DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''CHUONG_TRINH_SP_M'+@STRCOUNT+''')
		DROP TABLE CHUONG_TRINH_SP_M'+@STRCOUNT+'

SELECT ''MONTH'+@STRCOUNT+''' AS YEARMONTH, WEEK_TO
,CASE WHEN CHUONG_TRINH_SP IS NULL THEN ''LD-00-NONE'' ELSE CHUONG_TRINH_SP END AS CHUONG_TRINH_SP 
,DAO
,SUM(BAL_QD) AS BAL_QD
,COUNT(DISTINCT ACCTNO) AS COUNT_ACCOUNT INTO CHUONG_TRINH_SP_M'+@STRCOUNT+'
FROM DBO.LOAN_EOP_LOAI_QUICKWIN_'+@FULLDATE+'
WHERE DSA_2015=''OTHERS''
AND DAO IN (SELECT DAO FROM #DAO)
GROUP BY DAO,WEEK_TO,CASE WHEN CHUONG_TRINH_SP IS NULL THEN ''LD-00-NONE'' ELSE CHUONG_TRINH_SP END') 

EXEC(
'IF EXISTS
      (SELECT *
         FROM DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''CT_UUDAI_VAY_M'+@STRCOUNT+''')
		DROP TABLE CT_UUDAI_VAY_M'+@STRCOUNT+'

SELECT ''MONTH'+@STRCOUNT+''' AS YEARMONTH, WEEK_TO
,CASE WHEN CT_UUDAI_VAY IS NULL THEN ''00-KHONG THAM GIA CT UU DAI VAY'' ELSE CT_UUDAI_VAY END AS CT_UUDAI_VAY 
,DAO
,SUM(BAL_QD) AS BAL_QD
,COUNT(DISTINCT ACCTNO) AS COUNT_ACCOUNT INTO CT_UUDAI_VAY_M'+@STRCOUNT+'
FROM DBO.LOAN_EOP_LOAI_QUICKWIN_'+@FULLDATE+'
WHERE DSA_2015=''OTHERS''
AND DAO COLLATE DATABASE_DEFAULT IN (SELECT DAO FROM #DAO)
GROUP BY DAO,WEEK_TO,CASE WHEN CT_UUDAI_VAY IS NULL THEN ''00-KHONG THAM GIA CT UU DAI VAY'' ELSE CT_UUDAI_VAY END') 

exec(
'IF EXISTS
      (SELECT *
         FROM DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''SANPHAM_GROUP_M'+@strCount+''')
		drop table SANPHAM_GROUP_M'+@strCount+'

SELECT  ''MONTH'+@STRCOUNT+''' as yearmonth,WEEK_TO,SANPHAM_GROUP ,DAO,SUM(BAL_QD) AS BAL_QD,COUNT(DISTINCT ACCTNO) AS COUNT_ACCOUNT into SANPHAM_GROUP_M'+@strCount+'
FROM DBO.LOAN_EOP_LOAI_QUICKWIN_'+@fullDate+'
WHERE DSA_2015=''OTHERS''
AND DAO COLLATE DATABASE_DEFAULT IN (SELECT DAO FROM #DAO)
GROUP BY DAO,WEEK_TO,SANPHAM_GROUP') 

DECLARE @BEGINDATE NVARCHAR(8) = CONVERT(NVARCHAR(8), DATEADD(M,-1, DATEADD(D, 1, @MONTH1)),112)
DECLARE @ENDDATE NVARCHAR(8) = @FULLDATE
IF(@MONTH1 = '20160430') SET @ENDDATE = '20160430'

exec('
IF EXISTS
      (SELECT *
         FROM DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''LOAN_MTD_'+@ENDDATE+''')
		drop table LOAN_MTD_'+@ENDDATE+'

SELECT * INTO LOAN_MTD_'+@ENDDATE+'
FROM ANHCP.CEO_PERFORMANCE.DBO.LOAN_MTD_'+@BEGINDATE+'_'+@ENDDATE+'
where quickwin=''N''')

EXEC('
ALTER TABLE LOAN_MTD_'+@ENDDATE+'
ADD CT_UUDAI_VAY NVARCHAR(100)
,SUB_CATEGORY NVARCHAR(100)
,PRODUCT_CODE NVARCHAR(100)')

EXEC('
UPDATE A
SET A.CT_UUDAI_VAY=B.CT_UUDAI_VAY,A.SUB_CATEGORY=B.SUB_CATEGORY,A.PRODUCT_CODE=B.PRODUCT_CODE
FROM LOAN_MTD_'+@ENDDATE+' A, SERVER74.BICDATA.DBO.LNTB_DISBURSEMENT B
 WHERE A.ACCTNO=B.ACCTNO')
 
exec('
 alter table LOAN_MTD_'+@ENDDATE+'
 add SANPHAM1 nvarchar(100)
 ,SANPHAM2 nvarchar(100)
 ,SANPHAM3 nvarchar(100)
 ,SANPHAM4 nvarchar(100)
 ,SANPHAM nvarchar(100)
 ,SANPHAM_GROUP nvarchar(100)')
 
 exec('UPDATE LOAN_MTD_'+@ENDDATE+'
SET SANPHAM1= CASE WHEN CHUONG_TRINH_SP LIKE ''%BIL%'' OR CHUONG_TRINH_SP  LIKE ''%CLEAN%'' THEN ''Unsecured''
ELSE NULL END ')
 
 exec('UPDATE LOAN_MTD_'+@ENDDATE+'
SET SANPHAM= SANPHAM1')

exec('UPDATE LOAN_MTD_'+@ENDDATE+'
SET SANPHAM2= CASE WHEN CHUONG_TRINH_SP LIKE ''%CAR%'' THEN ''CAR''
ELSE NULL END ')
 
 exec('
 UPDATE LOAN_MTD_'+@ENDDATE+'
SET SANPHAM= SANPHAM2
WHERE SANPHAM IS NULL')
 
 exec('
UPDATE LOAN_MTD_'+@ENDDATE+'
SET SANPHAM3=''CAR''
WHERE [SUB_CATEGORY] IN (''101'',''102'',''103'',''104'',''105'',''106'')')

exec('
UPDATE LOAN_MTD_'+@ENDDATE+'
SET SANPHAM= SANPHAM3
WHERE SANPHAM IS NULL')
 
 exec('
UPDATE A
SET A.SANPHAM4=B.[Map lai (dung de map)]
FROM LOAN_MTD_'+@ENDDATE+' A, gianglh3.risk.dbo.MAP_SANPHAM_FINAL B
WHERE A.product_code COLLATE DATABASE_DEFAULT = B.[VPB LN PR]')
 
 exec('
UPDATE LOAN_MTD_'+@ENDDATE+'
SET SANPHAM=CASE WHEN SANPHAM4 IN (''Car'',''WC Revolving'',''WC Reducing'', ''WC Installment'', ''Valuable paper'')
THEN SANPHAM4
ELSE NULL END 
WHERE SANPHAM IS NULL')
 
 exec('
UPDATE LOAN_MTD_'+@ENDDATE+'
SET SANPHAM=''2.WORKING_CAPITAL''
WHERE SANPHAM4= N''Cho vay bổ sung vốn kinh doanh trung dài hạn thông thường''
and SANPHAM IS NULL')
 
 exec('
UPDATE LOAN_MTD_'+@ENDDATE+'
SET SANPHAM = CASE WHEN SANPHAM4 =''Project/Property Finance'' THEN SANPHAM4
ELSE NULL END 
WHERE SANPHAM IS NULL')
 
 exec('
UPDATE LOAN_MTD_'+@ENDDATE+'
SET SANPHAM = ''OTHER''
WHERE SANPHAM IS NULL')
 
 exec('
UPDATE  LOAN_MTD_'+@ENDDATE+'
SET SANPHAM_GROUP = CASE WHEN SANPHAM = ''CAR'' THEN ''1.CAR''
                    WHEN  SANPHAM IN (''WC reducing'',''WC installment'',''WC Revolving'',''2.WORKING_CAPITAL'') THEN ''2.WORKING_CAPITAL''
                                  WHEN  SANPHAM = ''Project/Property Finance'' THEN ''3.PROJECT/PROPERTY FINANCE''
                                  WHEN  SANPHAM = ''Unsecured'' THEN ''4.UNSECURED''
                                  WHEN  SANPHAM = ''CREDIT CARD'' THEN ''5.CREDIT_CARD''
                                  WHEN  SANPHAM in (''OTHER'',''Valuable paper'') THEN ''6.OTHER''
                                  ELSE NULL 
                                  END')
                 
exec(
'
IF EXISTS
      (SELECT *
         FROM DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''GIAI_NGAN_CTSP_M'+@strCount+''')
		drop table GIAI_NGAN_CTSP_M'+@strCount+'
		
		SELECT WEEK_TO
,CASE WHEN CHUONG_TRINH_SP IS NULL THEN ''LD-00-NONE'' ELSE CHUONG_TRINH_SP END AS CHUONG_TRINH_SP 
,DAO
,SUM(DSGN) AS DSGN
,COUNT(DISTINCT ACCTNO) AS COUNT_ACCOUNT into GIAI_NGAN_CTSP_M'+@strCount+'
FROM DBO.LOAN_MTD_'+@ENDDATE+'
WHERE DSA_2015=''OTHERS''
AND DAO COLLATE DATABASE_DEFAULT IN (SELECT DAO FROM #DAO)
GROUP BY DAO,WEEK_TO,CASE WHEN CHUONG_TRINH_SP IS NULL THEN ''LD-00-NONE'' ELSE CHUONG_TRINH_SP END') 

exec(
'IF EXISTS
      (SELECT *
         FROM DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''GN_CT_UUDAI_VAY_M'+@strCount+''')
		drop table GN_CT_UUDAI_VAY_M'+@strCount+'
		
	SELECT WEEK_TO
,CASE WHEN CT_UUDAI_VAY IS NULL THEN ''00-Khong tham gia CT uu dai vay'' ELSE CT_UUDAI_VAY END AS CT_UUDAI_VAY 
,DAO
,SUM(DSGN) AS DSGN
,COUNT(DISTINCT ACCTNO) AS COUNT_ACCOUNT into GN_CT_UUDAI_VAY_M'+@strCount+'
FROM DBO.LOAN_MTD_'+@ENDDATE+'
WHERE DSA_2015=''OTHERS''
AND DAO COLLATE DATABASE_DEFAULT IN (SELECT DAO FROM #DAO)
GROUP BY DAO,WEEK_TO,CASE WHEN CT_UUDAI_VAY IS NULL THEN ''00-Khong tham gia CT uu dai vay'' ELSE CT_UUDAI_VAY END') 

exec(
'IF EXISTS
      (SELECT *
         FROM DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''GN_SANPHAM_GROUP_M'+@strCount+''')
		drop table GN_SANPHAM_GROUP_M'+@strCount+'

SELECT WEEK_TO
,SANPHAM_GROUP 
,DAO
,SUM(DSGN) AS DSGN
,COUNT(DISTINCT ACCTNO) AS COUNT_ACCOUNT into GN_SANPHAM_GROUP_M'+@strCount+'
FROM DBO.LOAN_MTD_'+@ENDDATE+'
WHERE DSA_2015=''OTHERS''
AND DAO COLLATE DATABASE_DEFAULT IN (SELECT DAO FROM #DAO)
GROUP BY DAO,WEEK_TO,SANPHAM_GROUP') 

	--LAST DAY OF ANY MONTH AND YEAR
	SET @MONTH1 = DATEADD(S,-1,DATEADD(MM, DATEDIFF(M,0,@MONTH1)+2,0))
SET @COUNT = @COUNT + 1
END

EXEC('
IF EXISTS
      (SELECT *
         FROM DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''CHUONG_TRINH_SP'')
		DROP TABLE CHUONG_TRINH_SP

SELECT * INTO CHUONG_TRINH_SP FROM (
SELECT * FROM CHUONG_TRINH_SP_M1
UNION ALL
SELECT * FROM CHUONG_TRINH_SP_M2
UNION ALL
SELECT * FROM CHUONG_TRINH_SP_M3
UNION ALL
SELECT * FROM CHUONG_TRINH_SP_M4
UNION ALL
SELECT * FROM CHUONG_TRINH_SP_M5
UNION ALL
SELECT * FROM CHUONG_TRINH_SP_M6
UNION ALL
SELECT * FROM CHUONG_TRINH_SP_M7
UNION ALL
SELECT * FROM CHUONG_TRINH_SP_M8
UNION ALL
SELECT * FROM CHUONG_TRINH_SP_M9
UNION ALL
SELECT * FROM CHUONG_TRINH_SP_M10
UNION ALL
SELECT * FROM CHUONG_TRINH_SP_M11
UNION ALL
SELECT * FROM CHUONG_TRINH_SP_M12)A')

exec('
IF EXISTS
      (SELECT *
         FROM DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''CT_UUDAI_VAY'')
		drop table CT_UUDAI_VAY

select * into CT_UUDAI_VAY from (
select * from CT_UUDAI_VAY_M1
union all
select * from CT_UUDAI_VAY_M2
union all
select * from CT_UUDAI_VAY_M3
union all
select * from CT_UUDAI_VAY_M4
union all
select * from CT_UUDAI_VAY_M5
union all
select * from CT_UUDAI_VAY_M6
union all
select * from CT_UUDAI_VAY_M7
union all
select * from CT_UUDAI_VAY_M8
union all
select * from CT_UUDAI_VAY_M9
union all
select * from CT_UUDAI_VAY_M10
union all
select * from CT_UUDAI_VAY_M11
union all
select * from CT_UUDAI_VAY_M12)a')

exec('
IF EXISTS
      (SELECT *
         FROM DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''SANPHAM_GROUP'')
		drop table SANPHAM_GROUP

select * into SANPHAM_GROUP from (
select * from SANPHAM_GROUP_M1
union all
select * from SANPHAM_GROUP_M2
union all
select * from SANPHAM_GROUP_M3
union all
select * from SANPHAM_GROUP_M4
union all
select * from SANPHAM_GROUP_M5
union all
select * from SANPHAM_GROUP_M6
union all
select * from SANPHAM_GROUP_M7
union all
select * from SANPHAM_GROUP_M8
union all
select * from SANPHAM_GROUP_M9
union all
select * from SANPHAM_GROUP_M10
union all
select * from SANPHAM_GROUP_M11
union all
select * from SANPHAM_GROUP_M12)a')

exec('
IF EXISTS
      (SELECT *
         FROM DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''GIAI_NGAN_CTSP'')
		drop table GIAI_NGAN_CTSP

select * into GIAI_NGAN_CTSP from (
select * from GIAI_NGAN_CTSP_M1
union all
select * from GIAI_NGAN_CTSP_M2
union all
select * from GIAI_NGAN_CTSP_M3
union all
select * from GIAI_NGAN_CTSP_M4
union all
select * from GIAI_NGAN_CTSP_M5
union all
select * from GIAI_NGAN_CTSP_M6
union all
select * from GIAI_NGAN_CTSP_M7
union all
select * from GIAI_NGAN_CTSP_M8
union all
select * from GIAI_NGAN_CTSP_M9
union all
select * from GIAI_NGAN_CTSP_M10
union all
select * from GIAI_NGAN_CTSP_M11
union all
select * from GIAI_NGAN_CTSP_M12)a')

exec('
IF EXISTS
      (SELECT *
         FROM DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''GN_CT_UUDAI_VAY'')
		drop table GN_CT_UUDAI_VAY

select * into GN_CT_UUDAI_VAY from (
select * from GN_CT_UUDAI_VAY_M1
union all
select * from GN_CT_UUDAI_VAY_M2
union all
select * from GN_CT_UUDAI_VAY_M3
union all
select * from GN_CT_UUDAI_VAY_M4
union all
select * from GN_CT_UUDAI_VAY_M5
union all
select * from GN_CT_UUDAI_VAY_M6
union all
select * from GN_CT_UUDAI_VAY_M7
union all
select * from GN_CT_UUDAI_VAY_M8
union all
select * from GN_CT_UUDAI_VAY_M9
union all
select * from GN_CT_UUDAI_VAY_M10
union all
select * from GN_CT_UUDAI_VAY_M11
union all
select * from GN_CT_UUDAI_VAY_M12)a')

exec('
IF EXISTS
      (SELECT *
         FROM DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''GN_SANPHAM_GROUP'')
		drop table GN_SANPHAM_GROUP

select * into GN_SANPHAM_GROUP from (
select * from GN_SANPHAM_GROUP_M1
union all
select * from GN_SANPHAM_GROUP_M2
union all
select * from GN_SANPHAM_GROUP_M3
union all
select * from GN_SANPHAM_GROUP_M4
union all
select * from GN_SANPHAM_GROUP_M5
union all
select * from GN_SANPHAM_GROUP_M6
union all
select * from GN_SANPHAM_GROUP_M7
union all
select * from GN_SANPHAM_GROUP_M8
union all
select * from GN_SANPHAM_GROUP_M9
union all
select * from GN_SANPHAM_GROUP_M10
union all
select * from GN_SANPHAM_GROUP_M11
union all
select * from GN_SANPHAM_GROUP_M12)a')

--select distinct WEEK_TO from CHUONG_TRINH_SP order by WEEK_TO
--drop table #loan_new_raw

select CONVERT(nvarchar(6), CONVERT(DATE,WEEK_TO), 112) AS YEARMONTH, CHUONG_TRINH_SP,dao, SUM(COUNT_ACCOUNT) as quantity, sum(BAL_QD) as balance, 3 as group_product
 into #loan_raw
 from CHUONG_TRINH_SP 
 group by YEARMONTH, CHUONG_TRINH_SP, dao,WEEK_TO
 order by YEARMONTH, dao

--select distinct WEEK_TO  from CT_UUDAI_VAY order by WEEK_TO

 insert #loan_raw
select CONVERT(nvarchar(6), CONVERT(DATE,WEEK_TO), 112) AS YEARMONTH, CT_UUDAI_VAY,dao, SUM(COUNT_ACCOUNT) as quantity, sum(BAL_QD) as balance, 2 as group_product
 from CT_UUDAI_VAY 
 group by YEARMONTH, CT_UUDAI_VAY, dao,WEEK_TO
 order by YEARMONTH, dao
 
--select distinct WEEK_TO  from SANPHAM_GROUP order by WEEK_TO
--select * from SANPHAM_GROUP

 insert #loan_raw
select CONVERT(nvarchar(6), CONVERT(DATE,WEEK_TO), 112) AS YEARMONTH, SANPHAM_GROUP,dao, SUM(COUNT_ACCOUNT) as quantity, sum(BAL_QD) as balance, 1 as group_product
 from SANPHAM_GROUP 
 group by YEARMONTH, SANPHAM_GROUP, dao,WEEK_TO
 order by YEARMONTH, dao
 
--select distinct WEEK_TO  from GIAI_NGAN_CTSP order by WEEK_TO
--select * from GIAI_NGAN_CTSP

select CONVERT(nvarchar(6), CONVERT(DATE,WEEK_TO), 112) AS YEARMONTH, CHUONG_TRINH_SP,dao, SUM(count_account) as quantity, sum(dsgn) as balance, 3 as group_product
 into #loan_new_raw
 from GIAI_NGAN_CTSP 
 group by CHUONG_TRINH_SP, dao,WEEK_TO
 order by  dao
 
--select distinct WEEK_TO  from GN_CT_UUDAI_VAY order by WEEK_TO
--select * from GN_CT_UUDAI_VAY

 insert #loan_new_raw
select CONVERT(nvarchar(6), CONVERT(DATE,WEEK_TO), 112) AS YEARMONTH, CT_UUDAI_VAY,dao, SUM(count_account) as quantity, sum(dsgn) as balance, 2 as group_product
 from GN_CT_UUDAI_VAY 
 group by  CT_UUDAI_VAY, dao,WEEK_TO
 order by  dao
 
--select distinct WEEK_TO  from GN_SANPHAM_GROUP order by WEEK_TO
--select * from GN_SANPHAM_GROUP

 insert #loan_new_raw
select CONVERT(nvarchar(6), CONVERT(DATE,WEEK_TO), 112) AS YEARMONTH, SANPHAM_GROUP,dao, SUM(count_account) as quantity, sum(dsgn) as balance, 1 as group_product
 from GN_SANPHAM_GROUP 
 group by  SANPHAM_GROUP, dao,WEEK_TO
 order by  dao


 --select * from #loan_new_raw

 --select distinct YEARMONTH from #loan_raw

 
delete from TBL_SP_LOAN_EOP

declare @lastMonthDate date = (SELECT MAX_DATE_ENDMONTH FROM DATE_EM_REPORT)
--print CONVERT(NVARCHAR(6),(DATEADD(M, 0, @lastMonthDate)),112)


DECLARE @MONTH_1 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -1, @lastMonthDate)),112)
DECLARE @MONTH_2 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -2,@lastMonthDate)),112)
DECLARE @MONTH3 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -3, @lastMonthDate)),112)
DECLARE @MONTH4 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -4, @lastMonthDate)),112)
DECLARE @MONTH5 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -5, @lastMonthDate)),112)
DECLARE @MONTH6 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -6, @lastMonthDate)),112)
DECLARE @MONTH7 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -7, @lastMonthDate)),112)
DECLARE @MONTH8 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -8, @lastMonthDate)),112)
DECLARE @MONTH9 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -9, @lastMonthDate)),112)
DECLARE @MONTH10 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -10, @lastMonthDate)),112)
DECLARE @MONTH11 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -11, @lastMonthDate)),112)
DECLARE @MONTH0 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, 0, @lastMonthDate)),112)
print @MONTH0

exec('
select * into #tam1
from
(select yearmonth, dao,CHUONG_TRINH_SP,group_product, col, value from #loan_raw
cross apply
( select ''quantity'', cast(quantity as numeric) union all
  select ''balance'', cast(balance as numeric) ) c(col, value)) d
pivot
( max(value)
  for yearmonth in (['+@MONTH_1+'],['+@MONTH_2+'],['+@MONTH3+'],['+@MONTH4+'],['+@MONTH5+'],['+@MONTH6+'],['+@MONTH7+'],['+@MONTH8+'],['+@MONTH9+'],['+@MONTH10+'],['+@MONTH11+'],['+@MONTH0+'])) piv;

  insert TBL_SP_LOAN_EOP
  select dao, CHUONG_TRINH_SP, col,
  ['+@MONTH0+'] as [month0],['+@MONTH_1+'] as [month1],['+@MONTH_2+'] as [month2],['+@MONTH3+'] as [month3],['+@MONTH4+'] as [month4],['+@MONTH5+'] as [month5],['+@MONTH6+'] as [month6],['+@MONTH7+'] as [month7],['+@MONTH8+'] as [month8],['+@MONTH9+'] as [month9],['+@MONTH10+'] as [month10],['+@MONTH11+'] as [month11],1 as Group_Type,group_product  from #tam1

  select * into #tam2
from
(select yearmonth, dao,CHUONG_TRINH_SP,group_product, col, value from #loan_new_raw
cross apply
( select ''quantity'', cast(quantity as numeric) union all
  select ''balance'', cast(balance as numeric) ) c(col, value)) d
pivot
( max(value)
  for yearmonth in (['+@MONTH_1+'],['+@MONTH_2+'],['+@MONTH3+'],['+@MONTH4+'],['+@MONTH5+'],['+@MONTH6+'],['+@MONTH7+'],['+@MONTH8+'],['+@MONTH9+'],['+@MONTH10+'],['+@MONTH11+'],['+@MONTH0+'])) piv;

  insert TBL_SP_LOAN_EOP
  select dao, CHUONG_TRINH_SP, col,
  ['+@MONTH0+'] as [month0],['+@MONTH_1+'] as [month1],['+@MONTH_2+'] as [month2],['+@MONTH3+'] as [month3],['+@MONTH4+'] as [month4],['+@MONTH5+'] as [month5],['+@MONTH6+'] as [month6],['+@MONTH7+'] as [month7],['+@MONTH8+'] as [month8],['+@MONTH9+'] as [month9],['+@MONTH10+'] as [month10],['+@MONTH11+'] as [month11],2,group_product from #tam2
  ')
  --declare @count int = 0
  SET @count = 0

  while (@count <12)
  begin 
  exec('
  update TBL_SP_LOAN_EOP set month'+@count+'=0 where MONTH'+@count+' is null

  --update TBL_SP_LOAN_EOP set month'+@count+'=0 where col=''balance'' and group_type=2
  ')
  set @count = @count+1
  end
  --select Group_Type, group_product from TBL_SP_LOAN_EOP group by Group_Type, group_product


  --select * from TBL_SP_LOAN_EOP